package org.example.jdbcexamples.repository;


import org.example.jdbcexamples.dox.Address;
import org.example.jdbcexamples.dox.User;
import org.example.jdbcexamples.dto.UserCountDTO;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface UserRepository extends CrudRepository<User, String>{

    @Query("""
            select a.* from user u, address a
            where u.id=a.user_id and u.id=:uid
    """)
    List<Address> findAddressesById(String uid);

    @Query("""
        select * from user where id=:uid
    """)
    User findUserById(String uid);

    @Modifying
    @Query("update user set name=:newname where id=:uid")
    void updateUserName(String uid, String newname);

    @Modifying
    @Query(" delete from user where id=:uid")
    void deleteUserById(String uid);


    @Query("""
    select u.id as user_id,u.name,count(user_id) as count from user u left join address a on u.id=a.user_id
    group by u.id,u.name
    order by count asc
    """)
    List<UserCountDTO> findCount();
}

